package com.tappytaps.android.hc2010;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class Database {

	public static final String KEY_WORD_ROWID = "idWord";
	public static final String KEY_WORD_ORIGINAL = "original";
	public static final String KEY_WORD_TRANSLATION = "translation";
	public static final String KEY_WORD_PRIORITY = "priority";
	public static final String KEY_WORD_ID_CATEGORY = "idCategory"; // int
	public static final String KEY_WORD_LAST_UPDATE = "lastUpdate"; // in long (time in milis)
	public static final String KEY_WORD_CREATED = "created";

	public static final String TABLE_WORD = "word";

	private static final String TABLE_CREATE_WORD = "create table "
			+ TABLE_WORD + " (" + KEY_WORD_ROWID
			+ " integer primary key autoincrement, " 
			+ KEY_WORD_ORIGINAL	+ " varchar(200) not null," 
			+ KEY_WORD_TRANSLATION + " varchar(200) not null," 
			+ KEY_WORD_PRIORITY + " int not null,"
			+ KEY_WORD_ID_CATEGORY + " int not null," 
			+ KEY_WORD_LAST_UPDATE + " long not null,"
			+ KEY_WORD_CREATED	+ " long not null);";

	private static final String DATABASE_NAME = "vocer";
	private static final int DATABASE_VERSION = 1;

	private DatabaseHelper mDbHelper;
	private SQLiteDatabase mDb;
	private final Context mCtx;

	private static class DatabaseHelper extends SQLiteOpenHelper {

		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {

			db.beginTransaction();
			try {
				db.execSQL(TABLE_CREATE_WORD);
				db.setTransactionSuccessful();
			} finally {
				db.endTransaction();
			}
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {	
		}
	}

	public Database(Context ctx) {
		this.mCtx = ctx;
	}

	public Database open() throws SQLException {
		mDbHelper = new DatabaseHelper(mCtx);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}

	public void close() {
		mDbHelper.close();
	}

	public long createWord(String original, String translate, int priority, long idCategory) {
		ContentValues initialValues = new ContentValues();
		initialValues.put(KEY_WORD_ORIGINAL, original);
		initialValues.put(KEY_WORD_TRANSLATION, translate);
		initialValues.put(KEY_WORD_PRIORITY, priority);
		initialValues.put(KEY_WORD_ID_CATEGORY, idCategory);
		initialValues.put(KEY_WORD_LAST_UPDATE, System.currentTimeMillis());
		initialValues.put(KEY_WORD_CREATED, System.currentTimeMillis());
		return mDb.insert(TABLE_WORD, null, initialValues);
	}

	public boolean deleteWord(long rowId) {
		return mDb.delete(TABLE_WORD, KEY_WORD_ROWID + "=" + rowId, null) > 0;
	}

	public Cursor fetchAllWords() {

		return mDb.query(TABLE_WORD, new String[] { KEY_WORD_ROWID,
				KEY_WORD_ORIGINAL, KEY_WORD_TRANSLATION, KEY_WORD_ORIGINAL, KEY_WORD_PRIORITY, KEY_WORD_ID_CATEGORY, KEY_WORD_LAST_UPDATE, KEY_WORD_CREATED }, null, null, null, null,
				KEY_WORD_ROWID + " DESC");
	}

	public Cursor fetchWord(long rowId) throws SQLException {

		Cursor mCursor = mDb.query(true, TABLE_WORD, new String[] {
				KEY_WORD_ORIGINAL, KEY_WORD_TRANSLATION, KEY_WORD_ORIGINAL, KEY_WORD_PRIORITY, KEY_WORD_ID_CATEGORY, KEY_WORD_LAST_UPDATE, KEY_WORD_CREATED },
				KEY_WORD_ROWID + "=" + rowId, null, null, null, null, null);

		if (mCursor != null) {
			mCursor.moveToFirst();
		}
		return mCursor;

	}

	public boolean updateWord(long rowId, String original, String translate, int priority, int idCategory, long lastUpdate, long created) {
		ContentValues args = new ContentValues();

		args.put(KEY_WORD_ROWID, rowId);
		args.put(KEY_WORD_ORIGINAL, original);
		args.put(KEY_WORD_TRANSLATION, translate);
		args.put(KEY_WORD_PRIORITY, priority);
		args.put(KEY_WORD_ID_CATEGORY, idCategory);
		args.put(KEY_WORD_LAST_UPDATE, lastUpdate);
		args.put(KEY_WORD_CREATED, created);

		return mDb.update(TABLE_WORD, args, KEY_WORD_ROWID + "=" + rowId, null) > 0;
	}

}
